﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Reflection;
using System.Web;


namespace Tencent.OA.App
{
    public static class ExcelHelper
    {
        public static int m_SheetMaxLen = 65535;

        /// <summary>
        /// 将数据导出到excel
        /// </summary>
        /// <param name="pDt">数据源</param>
        /// <param name="pFileName">服务器文件保存路径</param>
        /// <param name="pSheet">工作表名</param>
        /// <param name="pSheetIdx">当前工作表序号（递归用）</param>
        /// <param name="pStart">当前工作表起始记录号（递归用）</param>
        /// <returns></returns>
        public static string CreateExcel(this DataTable pDt, string pFileName, string pSheet, int pSheetIdx, int pStart)
        {
            pFileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Temp\" + pFileName);

            if (!Directory.Exists(Path.GetDirectoryName(pFileName)))
            {
                Directory.CreateDirectory(Path.GetDirectoryName(pFileName));
            }
            if (File.Exists(pFileName))
            {
                System.IO.File.Delete(pFileName);
            }
            //System.IO.File.Copy(page.Request.PhysicalApplicationPath+ " \\TempFile\\tempList.xls",filename,true);
            string execlConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pFileName
                + ";Extended Properties='Excel 8.0;HDR=YES'";
            string sql = "";
            OleDbConnection oledbcn = new OleDbConnection(execlConnectionString);
            oledbcn.Open();
            OleDbTransaction trans = oledbcn.BeginTransaction();
            OleDbCommand oledbcmd = new OleDbCommand("", oledbcn);
            oledbcmd.Transaction = trans;

            pSheet = pSheet + (pSheetIdx == 0 ? "" : "(" + pSheetIdx.ToString() + ")"); //获得目前的工作表名
            sql = " Create table " + pSheet + " (";

            for (int i = 0; i < pDt.Columns.Count; i++)
            {
                pDt.Columns[i].ColumnName = pDt.Columns[i].Caption;
            }

            for (int i = 0; i < pDt.Columns.Count; i++)
            {
                sql += "[" + pDt.Columns[i].Caption + "] NTEXT,";
            }

            sql = sql.Substring(0, sql.Length - 1) + ")";
            try
            {
                oledbcmd.CommandText = sql;
                oledbcmd.ExecuteNonQuery();

                int end = ((pDt.Rows.Count > pStart + m_SheetMaxLen) ? (pStart + m_SheetMaxLen) : pDt.Rows.Count); //计算终止下标
                for (int j = pStart; j < end; j++)
                {
                    sql = "insert into [" + pSheet + "] values(";
                    for (int i = 0; i < pDt.Columns.Count; i++)
                    {
                        string values = "";
                        if (pDt.Rows[j][i] != System.DBNull.Value)
                        {
                            if (pDt.Columns[i].DataType == System.Type.GetType("System.DateTime"))
                            {

                                values = DateTime.Parse(pDt.Rows[j][i].ToString()).ToShortDateString();
                            }
                            else if (pDt.Columns[i].DataType == System.Type.GetType("System.Decimal") || pDt.Columns[i].DataType == System.Type.GetType("System.Double"))
                            {
                                values = Decimal.Parse(pDt.Rows[j][i].ToString()).ToString("N3");
                            }
                            else
                            {
                                values = Convert.ToString(pDt.Rows[j][i]);
                            }
                        }
                        sql += "'" + values.ToString().Replace("'", "''") + "',";                       
                    }
                    sql = sql.Substring(0, sql.Length - 1);
                    sql += ")";

                    oledbcmd.CommandText = sql;
                    oledbcmd.ExecuteNonQuery();
                }

                trans.Commit();
                oledbcn.Close();

                if (pDt.Rows.Count > pStart + m_SheetMaxLen) pDt.CreateExcel(pFileName, pSheet, pSheetIdx + 1, end);

                return String.Concat(pFileName);
            }
            catch (System.Exception e)
            {
                //m_Log.Error(e.ToString());
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                if (oledbcn.State != ConnectionState.Closed)
                    oledbcn.Close();
            }
        }
       /// <summary>
        /// 下载文件
       /// </summary>
       /// <param name="serverfilpath">本地文件名称</param>
       /// <param name="fileName">下载显示名称</param>
        public static void DownExcel(this DataTable pDt,string serverfilpath, string fileName)
        {           
            if (string.IsNullOrEmpty(fileName))
                throw new ArgumentNullException("fileName");

            if (!File.Exists(serverfilpath))
                return;

            FileInfo fileInfo = new FileInfo(serverfilpath);
            HttpContext context = HttpContext.Current;

            try
            {
                context.Response.Clear();
                context.Response.ClearHeaders();
                context.Response.ClearContent();
                context.Response.Buffer = false;

                context.Response.Charset = "utf-8";
                context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                context.Response.ContentType = "application/vnd.ms-excel";

                if (context.Request.Browser.Browser == "IE")
                    context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                else
                    context.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                context.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                context.Response.WriteFile(fileInfo.FullName, 0, fileInfo.Length);
                context.Response.Flush();
                context.Response.End();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                File.Delete(serverfilpath);
            }
        }
    }  
}
